package model;

import init.DBManagement;

import java.util.ArrayList;

import model.beans.CRNBean;
import model.beans.StudentBean;

public class CRN {
	public static int TEACHER = 0;
	public static int STUDENT = 1;
	public static ArrayList list(String str_code, int int_type ){
		ArrayList arrL_retVal = new ArrayList();
		String str_list = 
			" select   DISTINCT "+
			"              CICLO, "+
			"              CNR,"+
			"              PUESTO,"+
			"              MATERIA_TK,"+
			"              MATERIA_DESC,"+
			"              MAESTRO_TK,"+
			"              MAESTRO_NOMBRE,"+
			"              CARGA,"+
			"              TIPO,"+
			"              PLAN,"+
			"              GRADO,"+
			"              GRUPO,"+
			"              SGPO,"+
			"              TURNO,"+
			"              SECCION,"+
			"              DEPARTAMENTO,"+
			"              CARRERA"+
			" from     BASCRNV "+
			" where  ";
		if( int_type == TEACHER ){
			str_list += " MAESTRO_TK = '"+str_code+"' ";
		}else if(int_type == STUDENT){
			str_list += " ALUMNO_TK = '"+str_code+"' ";
		}
		DBManagement db = new DBManagement();
		db.executeQuery(str_list);
		CRNBean bean; 
		for( int int_i = 0; int_i < db.getNumRows(); int_i++){
			bean = new CRNBean(
					db.getItem(int_i,0),
					db.getItem(int_i,1),
					db.getItem(int_i,2),
					db.getItem(int_i,3),
					db.getItem(int_i,4),
					db.getItem(int_i,5),
					db.getItem(int_i,6),
					db.getItem(int_i,7),
					db.getItem(int_i,8),
					db.getItem(int_i,9),
					db.getItem(int_i,10),
					db.getItem(int_i,11),
					db.getItem(int_i,12),
					db.getItem(int_i,13),
					db.getItem(int_i,14),
					db.getItem(int_i,15),
					db.getItem(int_i,16)
			);
			arrL_retVal.add(bean);
		}
		return arrL_retVal;
	}
	
	public static CRNBean instance(String str_crncode, String str_ciclo){
		
		String str_list = 
			" select   DISTINCT "+
			"              CICLO, "+
			"              CNR,"+
			"              PUESTO,"+
			"              MATERIA_TK,"+
			"              MATERIA_DESC,"+
			"              MAESTRO_TK,"+
			"              MAESTRO_NOMBRE,"+
			"              CARGA,"+
			"              TIPO,"+
			"              PLAN,"+
			"              GRADO,"+
			"              GRUPO,"+
			"              SGPO,"+
			"              TURNO,"+
			"              SECCION,"+
			"              DEPARTAMENTO,"+
			"              CARRERA"+
			" from     BASCRNV "+
			" where  CNR = '" + str_crncode +"' " +
			" and CICLO = '" + str_ciclo + "'" ;
		
			DBManagement db = new DBManagement();
			db.executeQuery(str_list);
			CRNBean bean = new CRNBean(
						db.getItem(0,0),
						db.getItem(0,1),
						db.getItem(0,2),
						db.getItem(0,3),
						db.getItem(0,4),
						db.getItem(0,5),
						db.getItem(0,6),
						db.getItem(0,7),
						db.getItem(0,8),
						db.getItem(0,9),
						db.getItem(0,10),
						db.getItem(0,11),
						db.getItem(0,12),
						db.getItem(0,13),
						db.getItem(0,14),
						db.getItem(0,15),
						db.getItem(0,16));
		
			return bean;
	}
	
	public static ArrayList students(CRNBean crn ){
		ArrayList arrL_retVal = new ArrayList();
		String str_students = 
			" SELECT  ALUMNO_TK, "+
			"               ALUMNO_NOMBRE "+
			" FROM    BASCRNV "+
			" WHERE CICLO = '"+crn.getCiclo()+"' AND "+
			"              CNR = '"+crn.getCnr()+"'"+
			" ORDER BY ALUMNO_TK ";
		DBManagement db = new DBManagement();
		db.executeQuery(str_students);
		StudentBean bean = null;
		for( int int_i = 0; int_i < db.getNumRows(); int_i++){
			bean = new StudentBean(db.getItem(int_i, 0), db.getItem(int_i, 1), crn.getCiclo(), crn.getCnr());
			arrL_retVal.add(bean);
		}
		db = null;
		return arrL_retVal;
	}
	
	public static ArrayList  putScored(CRNBean crn, ArrayList arL_students){
		ArrayList arr_retVal = new ArrayList();
		String str_scored = 
			" select   v.alumno_tk, "+
			"              c.cal_tech_key, "+
			"              a.calificacion "+
			" from      plamstb p "+
			"                    inner join placalr c "+
			"                    on p.pla_tech_key = c.plan_tk "+
			"                    inner join bascrnv v "+
			"                    on v.ciclo = '"+crn.getCiclo()+"' and "+
			"                         v.cnr = '"+crn.getCnr()+"' and "+
			"                         v.materia_tk = p.materia_tk "+
			"                   left join placaar a "+
			"                   on c.cal_tech_key = a.criterio_tk and  "+
			"                        a.alumno_tk = v.alumno_tk "+
			" where     p.materia_tk = '"+crn.getMateria_tk()+"' "+
			" order by v.alumno_tk,  "+
			"                cal_tech_key ";
		DBManagement db = new DBManagement();
		db.executeQuery(str_scored);
		String str_lastStudent = "";
		StudentBean bean = null;
		ArrayList scored = null;
		int int_j = 0;
		for( int int_i = 0; int_i < db.getNumRows(); int_i++){
			if( !str_lastStudent.equals(db.getItem(int_i,0)) ){
				if( int_i > 0 ){
					bean.setScored(scored);
					arr_retVal.add(bean);
				}
				bean = (StudentBean)arL_students.get(int_j++);
				scored = new ArrayList();
			}
			String str_calificacion = db.getItem(int_i,2);
			if( str_calificacion == null || str_calificacion.toLowerCase().equals("null")){
				 str_calificacion = "0";
			}
			scored.add(str_calificacion);
			str_lastStudent = db.getItem(int_i,0);
		}
		if(int_j>0){
			bean.setScored(scored);
			arr_retVal.add(bean);
		}
		db = null;
		return arr_retVal;
	}
	
	public static StudentBean student(String str_studentId ){
		StudentBean bn_student = null;
		String str_select = 
		" SELECT NOMBRE "+
		" FROM    BASALMR "+
		" WHERE CODIGO = '"+str_studentId+"'";
		
		DBManagement db = new DBManagement();
		db.executeQuery(str_select);
		bn_student = new StudentBean(str_studentId,db.getItem(0, 0), null, null);
		
		return bn_student;
	}
}
